using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for SecurityFilter
/// </summary>
public class SecurityFilter
{
    HDDatabase HDD = new HDDatabase();

    public SecurityFilter()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public string GetSubOrdinates(string user_afrowid)
    {
        string sub_af_row_id = "";
        string temp_sub_af_row_id = "'" + user_afrowid + "'";

        string strSql = "Select p_usr.af_row_id as [AFID],reporting_to as [RP]  from sy_usr p_usr ";
        DataTable dt_FN = HDD.GetDataTable(strSql);

        DataRow[] filterrow;

        do
        {
            filterrow = dt_FN.Select("RP in (" + temp_sub_af_row_id + ")");

            temp_sub_af_row_id = "";

            foreach (DataRow row in filterrow)
            {
                sub_af_row_id = sub_af_row_id + "'" + row["AFID"].ToString() + "',";
                temp_sub_af_row_id = temp_sub_af_row_id + "'" + row["AFID"].ToString() + "',";
            }

            if (temp_sub_af_row_id.Trim().Length > 0)
            {
                temp_sub_af_row_id = temp_sub_af_row_id.Substring(0, temp_sub_af_row_id.Length - 1);
            }

        } while (temp_sub_af_row_id.Length > 0);

        if (sub_af_row_id.Trim().Length > 0)
        {
            sub_af_row_id = sub_af_row_id.Substring(0, sub_af_row_id.Length - 1);
            sub_af_row_id = "," + sub_af_row_id;
        }

        return sub_af_row_id;
    }

    public DataSet FilterDataset(DataSet dseObj)
    {
        try
        {
            //string cust_filter = HDD.GetColumnValue("select custodian_filter from sy_usr where af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
            string cust_filter = CommonFunctions.GetColValueFromPageInfo((HttpContext.Current.Request.QueryString["pgs_id"] != null ? HttpContext.Current.Request.QueryString["pgs_id"].ToString() : ""), "sy_usr", "custodian_filter", "af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
            string safadmin_row_id = HDD.GetColumnValue("select user_id from sy_usr_rol where role_id='e7029dafbe379f85'");
            string sRol_af_id = HDD.GetColumnValue("select role_id from sy_usr_rol where user_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
            //string sRol_id = HDD.GetColumnValue("select role_id from sy_rol where af_row_id='" + sRol_af_id + "'");

            DataRow[] filterRowArray;
            DataSet dseFilter = new DataSet();
            dseFilter = dseObj.Clone();

            //if (sRol_id == "afadmin:accessrights")
            if (sRol_af_id == "e7029dafbe379f85") // Added by 'Imran Gani' on 08-May2014, to avoid role_id
            {
                if (dseObj.Tables[0].Columns.Contains("af_c_by"))
                    dseObj.Tables[0].Columns.Remove("af_c_by");
                return dseObj;
            }
            else
            {
                //-----------Modified by 'Imran Gani' (copied from RakaTech) on 05-June-2013 for Org Security-----------
                //if (cust_filter.ToLower() == "ar")
                //{
                filterRowArray = dseObj.Tables[0].Select("af_c_by <> '" + safadmin_row_id + "' or af_c_by is null");

                foreach (DataRow row in filterRowArray)
                {
                    dseFilter.Tables[0].ImportRow(row);
                }

                if (dseFilter.Tables[0].Columns.Contains("af_c_by"))
                    dseFilter.Tables[0].Columns.Remove("af_c_by");

                return dseFilter;
                //}
                //else
                //{
                //    filterRowArray = dseObj.Tables[0].Select("af_c_by = '" + HttpContext.Current.Session["user_afrowid"].ToString() + "' or af_c_by is null"); // modified by srivatsan on July 31 2009 included null values

                //    foreach (DataRow row in filterRowArray)
                //    {
                //        dseFilter.Tables[0].ImportRow(row);
                //    }

                //    if (dseFilter.Tables[0].Columns.Contains("af_c_by"))
                //        dseFilter.Tables[0].Columns.Remove("af_c_by");

                //    return dseFilter;
                //}
                //----------------End for Org Security------------------
            }
        }

        catch (Exception e)
        {
            LogWriter.WriteLog("Error in FilterDataset function : " + e.Message);
            return dseObj;
        }
    }

    public DataSet GetFilterDataset(string strSql)
    {
        //string cust_filter = HDD.GetColumnValue("select custodian_filter from sy_usr where af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
        string cust_filter = CommonFunctions.GetColValueFromPageInfo((HttpContext.Current.Request.QueryString["pgs_id"] != null ? HttpContext.Current.Request.QueryString["pgs_id"].ToString() : ""), "sy_usr", "custodian_filter", "af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");

        if (cust_filter == "") return HDD.GetDataset(strSql);// March 30 2008

        DataSet dseFilter = new DataSet();

        try
        {
            //strSql = strSql.Replace(" from ", ", created_by as af_c_by, modified_at as af_m_at from ");

            // Added by srivatsan on June 15 2009 for owner id field
            strSql = strSql.Replace(" from ", ", owner_id as af_c_by, modified_at as af_m_at from ");


            return RemoveDuplicateRecords(FilterDataset(HDD.GetDataset(strSql)));
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in GetQuery function : " + e.Message + ", " + strSql);
            return null;
        }
    }

    public DataSet GetFilterDataset(string strSql, bool isSearch)
    {
       // string cust_filter = HDD.GetColumnValue("select custodian_filter from sy_usr where af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");

        //if (cust_filter == "") return HDD.GetDataset(strSql);// March 30 2008

        DataSet dseFilter = new DataSet();

        try
        {
            //strSql = strSql.Replace(" from ", ", created_by as af_c_by, modified_at as af_m_at from ");

            // Added by srivatsan on June 15 2009 for owner id field
            strSql = strSql.Replace(" from ", ", owner_id as af_c_by, modified_at as af_m_at from ");

            return RemoveDuplicateRecords(FilterDataset(HDD.GetDataset(strSql)),isSearch);
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in GetQuery function : " + e.Message + ", " + strSql);
            return null;
        }
    }

    public DataSet RemoveDuplicateRecords(DataSet SourceDataset)
    {
        int i = 0;
        DataTable SourceTable = new DataTable();
        DataSet FinalDataset = new DataSet();
        SourceTable = SourceDataset.Tables[0];
        string[] FieldNames = new string[SourceTable.Columns.Count];

        try
        {
            foreach (DataColumn col in SourceTable.Columns)
            {
                FieldNames[i] = col.ColumnName;
                i++;
            }
            FinalDataset.Tables.Add(SelectDistinct(SourceTable, FieldNames));

            DataRow[] filterRowArray = FinalDataset.Tables[0].Select("", "af_m_at desc");

            DataSet dsTemp = FinalDataset.Clone();

            foreach (DataRow row in filterRowArray)
            {
                dsTemp.Tables[0].ImportRow(row);
            }

            if (dsTemp.Tables[0].Columns.Contains("af_m_at"))
                dsTemp.Tables[0].Columns.Remove("af_m_at");

            FinalDataset = dsTemp.Copy();

            dsTemp.Dispose();
            dsTemp = null;

            return FinalDataset;
        }

        catch (Exception e)
        {
            LogWriter.WriteLog("Error in RemoveDuplicateRecords function : " + e.Message);
            return FinalDataset;
        }
    }

    public DataSet RemoveDuplicateRecords(DataSet SourceDataset, bool IsSearch)
    {
        int i = 0;
        DataTable SourceTable = new DataTable();
        DataSet FinalDataset = new DataSet();
        SourceTable = SourceDataset.Tables[0];
        string[] FieldNames = new string[SourceTable.Columns.Count];

        try
        {
            foreach (DataColumn col in SourceTable.Columns)
            {
                FieldNames[i] = col.ColumnName;
                i++;
            }
            FinalDataset.Tables.Add(SelectDistinct(SourceTable, FieldNames));

            DataRow[] filterRowArray = null;
            
            if (IsSearch)
                filterRowArray = FinalDataset.Tables[0].Select("",FinalDataset.Tables[0].Columns[0].ColumnName + " asc");
            else
                filterRowArray = FinalDataset.Tables[0].Select("", "af_m_at desc");

            DataSet dsTemp = FinalDataset.Clone();

            foreach (DataRow row in filterRowArray)
            {
                dsTemp.Tables[0].ImportRow(row);
            }

            if (dsTemp.Tables[0].Columns.Contains("af_m_at"))
                dsTemp.Tables[0].Columns.Remove("af_m_at");

            FinalDataset = dsTemp.Copy();

            dsTemp.Dispose();
            dsTemp = null;

            return FinalDataset;
        }

        catch (Exception e)
        {
            LogWriter.WriteLog("Error in RemoveDuplicateRecords function : " + e.Message);
            return FinalDataset;
        }
    }

    private static DataTable SelectDistinct(DataTable SourceTable, params string[] FieldNames)
    {
        object[] lastValues;
        DataTable newTable;
        DataRow[] orderedRows;

        if (FieldNames == null || FieldNames.Length == 0)
            throw new ArgumentNullException("FieldNames");

        lastValues = new object[FieldNames.Length];
        newTable = new DataTable();

        foreach (string fieldName in FieldNames)
            newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType);

        orderedRows = SourceTable.Select("", "[" + string.Join(", ", FieldNames).Replace(", ", "], [") + "]");

        foreach (DataRow row in orderedRows)
        {
            if (!fieldValuesAreEqual(lastValues, row, FieldNames))
            {
                newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames));
                setLastValues(lastValues, row, FieldNames);
            }
        }
        return newTable;
    }

    private static bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
    {
        bool areEqual = true;

        for (int i = 0; i < fieldNames.Length; i++)
        {
            if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
            {
                areEqual = false;
                break;
            }
        }

        return areEqual;
    }

    private static DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
    {
        foreach (string field in fieldNames)
            newRow[field] = sourceRow[field];

        return newRow;
    }

    private static void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
    {
        for (int i = 0; i < fieldNames.Length; i++)
            lastValues[i] = sourceRow[fieldNames[i]];
    }

    public string QueryForAFAdmin()
    {
        string sSQL = "";
        try
        {
            if (HttpContext.Current.Session["roleid"].ToString() != "e7029dafbe379f85")
            {
                DataTable dtAdmin = HDD.GetDataTable("select user_id from sy_usr_rol where role_id='e7029dafbe379f85'");
                foreach (DataRow dr in dtAdmin.Rows)
                {
                    sSQL = sSQL + "'" + dr[0].ToString() + "',";
                }

                sSQL = sSQL.Trim(',');
                if (sSQL.Trim() != "")
                    sSQL = "owner_id not in (" + sSQL + ") ";
            }
        }

        catch
        {
        }
        return sSQL;
    }

    public string QueryForCustodianFilter()
    {
        string sSQL = "";
        try
        {
            if (HttpContext.Current.Session["roleid"].ToString() != "e7029dafbe379f85")
            {
                string sCustodianFilter = CommonFunctions.GetColValueFromPageInfo((HttpContext.Current.Request.QueryString["pgs_id"] == null ? "" : HttpContext.Current.Request.QueryString["pgs_id"].ToString()), "sy_usr", "custodian_filter", "af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
                if (sCustodianFilter == "") sCustodianFilter = HDD.GetColumnValue("select custodian_filter from sy_usr where af_row_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
                if (sCustodianFilter.ToLower() != "ar")
                {
                    sSQL = "'" + HttpContext.Current.Session["user_afrowid"].ToString() + "'";
                }
                if (sSQL.Trim() != "")
                {
                    if (sCustodianFilter.ToLower() != "ar")
                    {
                        sSQL = " owner_id in (" + sSQL + ") or owner_id is null";
                    }
                    else
                    {
                        sSQL = "(owner_id in (" + sSQL + ") or owner_id is null)";
                    }
                }
                else
                {
                   sSQL = "or owner_id is null";
                }

                ////if (sCustodianFilter == "ar")
                ////{
                ////    sSQL = "1=1 or owner_id is null";
                ////}
            }
        }
        catch
        {
        }
        return sSQL;
    }

    public DataTable ApplySecurityforDataTable(DataTable dt)
    {
        DataTable dtresult = null;
        if (dt != null)
        {
            if (dt.Rows.Count > 0)
            {
                if (dt.Columns.Contains("owner_id"))
                {
                    dtresult = dt.Clone().Copy();
                    SecurityFilter objSec = new SecurityFilter();
                    string sAFAdmin = objSec.QueryForAFAdmin();
                    string sCustodianFilter = objSec.QueryForCustodianFilter();
                    if (sCustodianFilter.Trim() != "")
                        if (!sCustodianFilter.StartsWith("or"))
                        {
                            if (sAFAdmin.Trim() != "")
                            {
                                sAFAdmin = "(" + sAFAdmin + " and " + sCustodianFilter + ") ";
                            }
                            else
                            {
                                sAFAdmin = "(" + sCustodianFilter + ") ";
                            }
                        }
                        else
                        {
                            if (sAFAdmin.Trim() != "")
                            {
                                sAFAdmin = "(" + sAFAdmin + " " + sCustodianFilter + ") ";
                            }
                            else
                            {
                                sAFAdmin = "(" + sCustodianFilter + ") ";
                            }

                        }


                    if (sAFAdmin != "")
                    {
                        DataRow[] drr = dt.Select(sAFAdmin);

                        if (drr.Length > 0)
                        {
                            foreach (DataRow datarow in drr)
                            {
                                //dtresult.Rows.Add(datarow);
                                dtresult.ImportRow(datarow);
                            }
                        }
                    }
                    else 
                    {
                        dtresult = dt;
                    }

                }
                else // if not owner field in the dataset show all the records
                {
                    dtresult = dt;
                }
            }
            else
            {
                dtresult = dt;
            }
        }

        return dtresult;
    }

    public string applysecurity(string sql)
    {

        try
        {
            SecurityFilter objSec = new SecurityFilter();
            string sAFAdmin = objSec.QueryForAFAdmin();
            string sCustodianFilter = ""; // = objSec.QueryForCustodianFilter(); // commented for Org Security
            if (sCustodianFilter.Trim() != "")
                if (!sCustodianFilter.StartsWith("or"))
                {
                    sAFAdmin += " and " + sCustodianFilter;
                }
                else
                {
                    //if (QuickSearchSQL.Trim() == "")  // if no search exists show all no owner records
                    //{
                    sAFAdmin = "(" + sAFAdmin + " " + sCustodianFilter + ") ";
                    //sAFAdmin += " " + sCustodianFilter;
                    // }
                }
            //-----------Added by 'Imran Gani' (copied from RakaTech) on 05-June-2013 for Org Security-----------
            string page_id = "";
            string strPagesetRowId = "";
            if (HttpContext.Current.Request.QueryString["pgs_id"] != null)
            {
                //strPagesetRowId = HDD.GetColumnValue("select af_row_id from sy_pgs where pageset_id='" + HttpContext.Current.Request.QueryString["pgs_id"].ToString() + "'");
                strPagesetRowId = HttpContext.Current.Request.QueryString["pgs_id"].ToString();
                page_id = HDD.GetColumnValue("select page_id from sy_pgs_mem where pageset_id='" + strPagesetRowId + "'");
            }
            string modelConString = "";
            modelConString = HttpContext.Current.Session["servercon"].ToString();
            string str_OwnerId = "";
            string str_BusinessUnit = "";
            if (HttpContext.Current.Session["dbcon"].ToString() != modelConString && sAFAdmin != "")
            {
                BusinessUnit.Access ACS = new BusinessUnit.Access(HttpContext.Current.Session["userid"].ToString(), HDD.GetConnection().ConnectionString, page_id);
                str_OwnerId = ACS.AccessableOwners;
                str_BusinessUnit = ACS.AccessableBuUnits;
            }
            //----------------End for Org Security------------------
            if (sql.Contains("order by"))
            {
                if (sAFAdmin.Trim() != "")
                {
                    if (sql.Contains("where"))
                        sql = sql.Replace("order by", " and "+ sAFAdmin + " order by ");
                    else
                        sql = sql.Replace("order by", "where " + sAFAdmin + " order by ");
                }
                //-----------Added by 'Imran Gani' (copied from RakaTech) on 05-June-2013 for Org Security-----------
                if (str_OwnerId != "")
                {
                    if (sql.Contains("where"))
                        sql = sql.Replace("order by", " " + str_OwnerId + " or owner_id is null " + " order by ");
                    else
                        sql = sql.Replace("order by", " where " + str_OwnerId.Remove(0, 5) + " or owner_id is null " + " order by ");
                }
                //----------------End for Org Security------------------
            }
            else
            {
                if (sAFAdmin.Trim() != "")
                {
                    if (sql.Contains("where "))
                    {
                        sql = sql + " and " + sAFAdmin;
                    }
                    else
                    {
                        sql = sql + " where " + sAFAdmin;
                    }
                }
                //-----------Added by 'Imran Gani' (copied from RakaTech) on 05-June-2013 for Org Security-----------
                if (str_OwnerId != "")
                {
                    if (sql.Contains("where") == true)
                        sql += " " + str_OwnerId + " or owner_id is null ";
                    else
                        sql += " where " + str_OwnerId.Remove(0, 5) + " or owner_id is null ";
                }
                //----------------End for Org Security------------------
            }


        }
        catch
        {

        }
        return sql;
    }
}
